package com.javaxiaobear.module.tool.gen.mapper;

import static com.javaxiaobear.module.tool.gen.domain.table.GenTableTableDef.GEN_TABLE;

import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.javaxiaobear.base.common.utils.PageUtils;
import com.javaxiaobear.module.tool.gen.domain.GenTable;
import com.javaxiaobear.module.tool.gen.domain.GenTableColumn;
import com.mybatisflex.core.BaseMapper;
import com.mybatisflex.core.paginate.Page;
import com.mybatisflex.core.query.QueryWrapper;
import java.util.*;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * 业务 数据层
 *
 * @author javaxiaobear
 */
public interface GenTableMapper extends BaseMapper<GenTable> {
  /**
   * 查询业务列表
   *
   * @param genTable 业务信息
   * @return 业务集合
   */
  default List<GenTable> selectGenTableList(GenTable genTable) {
    QueryWrapper queryWrapper = QueryWrapper.create();

    if (genTable.getTableName() != null && !genTable.getTableName().isEmpty()) {
      queryWrapper.and(GEN_TABLE.TABLE_NAME.like(genTable.getTableName()));
    }

    if (genTable.getTableComment() != null && !genTable.getTableComment().isEmpty()) {
      queryWrapper.and(GEN_TABLE.TABLE_COMMENT.like(genTable.getTableComment()));
    }

    // 时间范围查询
    if (genTable.getParams() != null) {
      if (genTable.getParams().get("beginTime") != null
          && !genTable.getParams().get("beginTime").toString().isEmpty()) {
        queryWrapper.and(GEN_TABLE.CREATE_TIME.ge(genTable.getParams().get("beginTime")));
      }

      if (genTable.getParams().get("endTime") != null
          && !genTable.getParams().get("endTime").toString().isEmpty()) {
        queryWrapper.and(GEN_TABLE.CREATE_TIME.le(genTable.getParams().get("endTime")));
      }
    }

    return selectListByQuery(queryWrapper);
  }

  default Page<GenTable> selectGenTablePage(GenTable genTable) {
    QueryWrapper queryWrapper = QueryWrapper.create();

    if (genTable.getTableName() != null && !genTable.getTableName().isEmpty()) {
      queryWrapper.and(GEN_TABLE.TABLE_NAME.like(genTable.getTableName()));
    }

    if (genTable.getTableComment() != null && !genTable.getTableComment().isEmpty()) {
      queryWrapper.and(GEN_TABLE.TABLE_COMMENT.like(genTable.getTableComment()));
    }

    // 时间范围查询
    if (genTable.getParams() != null) {
      if (genTable.getParams().get("beginTime") != null
          && !genTable.getParams().get("beginTime").toString().isEmpty()) {
        queryWrapper.and(GEN_TABLE.CREATE_TIME.ge(genTable.getParams().get("beginTime")));
      }

      if (genTable.getParams().get("endTime") != null
          && !genTable.getParams().get("endTime").toString().isEmpty()) {
        queryWrapper.and(GEN_TABLE.CREATE_TIME.le(genTable.getParams().get("endTime")));
      }
    }

    return paginate(PageUtils.getPage(), queryWrapper);
  }

  /**
   * 查询据库列表
   *
   * @param genTable 业务信息
   * @return 数据库表集合
   */
  default List<GenTable> selectDbTableList(GenTable genTable) {
    QueryWrapper query =
        QueryWrapper.create()
            .select("table_name", "table_comment", "create_time", "update_time")
            .from("information_schema.tables")
            .where("table_schema = (select database())")
            .and("table_name NOT LIKE ?", "qrtz_%")
            .and("table_name NOT LIKE ?", "gen_%")
            .and("table_name NOT IN (select table_name from gen_table)");

    // 🔹 Hutool 判断是否有值
    if (StrUtil.isNotBlank(genTable.getTableName())) {
      query.and("lower(table_name) like lower(concat('%', ?, '%'))", genTable.getTableName());
    }
    if (StrUtil.isNotBlank(genTable.getTableComment())) {
      query.and("lower(table_comment) like lower(concat('%', ?, '%'))", genTable.getTableComment());
    }

    // 🔹 Hutool 处理 params
    if (MapUtil.isNotEmpty(genTable.getParams())) {
      Object beginTime = genTable.getParams().get("beginTime");
      Object endTime = genTable.getParams().get("endTime");

      if (ObjectUtil.isNotEmpty(beginTime)) {
        query.and("date_format(create_time,'%y%m%d') >= date_format(?, '%y%m%d')", beginTime);
      }
      if (ObjectUtil.isNotEmpty(endTime)) {
        query.and("date_format(create_time,'%y%m%d') <= date_format(?, '%y%m%d')", endTime);
      }
    }

    query.orderBy("create_time desc");
    return this.selectListByQuery(query);
  }

  default Page<GenTable> selectDbTablePage(GenTable genTable) {
    QueryWrapper query =
        QueryWrapper.create()
            .select("table_name", "table_comment", "create_time", "update_time")
            .from("information_schema.tables")
            .where("table_schema = (select database())")
            .and("table_name NOT LIKE ?", "qrtz_%")
            .and("table_name NOT LIKE ?", "gen_%")
            .and("table_name NOT IN (select table_name from gen_table)");

    // 🔹 Hutool 判断是否有值
    if (StrUtil.isNotBlank(genTable.getTableName())) {
      query.and("lower(table_name) like lower(concat('%', ?, '%'))", genTable.getTableName());
    }
    if (StrUtil.isNotBlank(genTable.getTableComment())) {
      query.and("lower(table_comment) like lower(concat('%', ?, '%'))", genTable.getTableComment());
    }

    // 🔹 Hutool 处理 params
    if (MapUtil.isNotEmpty(genTable.getParams())) {
      Object beginTime = genTable.getParams().get("beginTime");
      Object endTime = genTable.getParams().get("endTime");

      if (ObjectUtil.isNotEmpty(beginTime)) {
        query.and("date_format(create_time,'%y%m%d') >= date_format(?, '%y%m%d')", beginTime);
      }
      if (ObjectUtil.isNotEmpty(endTime)) {
        query.and("date_format(create_time,'%y%m%d') <= date_format(?, '%y%m%d')", endTime);
      }
    }

    query.orderBy("create_time desc");
    return this.paginate(PageUtils.getPage(), query);
  }

  /**
   * 查询据库列表
   *
   * @param tableNames 表名称组
   * @return 数据库表集合
   */
  default List<GenTable> selectDbTableListByNames(String[] tableNames) {
    QueryWrapper query =
        QueryWrapper.create()
            .select("table_name", "table_comment", "create_time", "update_time")
            .from("information_schema.tables")
            .where("table_schema = (select database())")
            .and("table_name NOT LIKE ?", "qrtz_%")
            .and("table_name NOT LIKE ?", "gen_%")
            .in("table_name", ListUtil.of(tableNames));

    return this.selectListByQuery(query);
  }

  /**
   * 查询所有表信息
   *
   * @return 表信息集合
   */
  default List<GenTable> selectGenTableAll() {
    // 🔹 使用 JoinWrapper 构造 SQL
    QueryWrapper wrapper =
        QueryWrapper.create()
            .select(
                "t.table_id, t.table_name, t.table_comment, t.sub_table_name, t.sub_table_fk_name, "
                    + "t.class_name, t.tpl_category, t.tpl_web_type, t.package_name, t.module_name, "
                    + "t.business_name, t.function_name, t.function_author, t.options, t.remark, "
                    + "c.column_id, c.column_name, c.column_comment, c.column_type, c.java_type, "
                    + "c.java_field, c.is_pk, c.is_increment, c.is_required, c.is_insert, c.is_edit, "
                    + "c.is_list, c.is_query, c.query_type, c.html_type, c.dict_type, c.sort")
            .from("gen_table t")
            .leftJoin("gen_table_column c")
            .on("t.table_id = c.table_id")
            .orderBy("c.sort");

    // 🔹 执行 SQL，得到 flat list（笛卡尔结果）
    List<Map> rows = this.selectListByQueryAs(wrapper, Map.class);

    // 🔹 把一对多关系组装回去
    Map<Long, GenTable> tableMap = new LinkedHashMap<>();
    for (Map row : rows) {
      Long tableId = Convert.toLong(row.get("table_id"));
      if (!tableMap.containsKey(tableId)) {
        GenTable table = new GenTable();
        table.setTableId(tableId);
        table.setTableName(Convert.toStr(row.get("table_name")));
        table.setTableComment(Convert.toStr(row.get("table_comment")));
        table.setSubTableName(Convert.toStr(row.get("sub_table_name")));
        table.setSubTableFkName(Convert.toStr(row.get("sub_table_fk_name")));
        table.setClassName(Convert.toStr(row.get("class_name")));
        table.setTplCategory(Convert.toStr(row.get("tpl_category")));
        table.setTplWebType(Convert.toStr(row.get("tpl_web_type")));
        table.setPackageName(Convert.toStr(row.get("package_name")));
        table.setModuleName(Convert.toStr(row.get("module_name")));
        table.setBusinessName(Convert.toStr(row.get("business_name")));
        table.setFunctionName(Convert.toStr(row.get("function_name")));
        table.setFunctionAuthor(Convert.toStr(row.get("function_author")));
        table.setOptions(Convert.toStr(row.get("options")));
        table.setRemark(Convert.toStr(row.get("remark")));
        table.setColumns(new ArrayList<>());
        tableMap.put(tableId, table);
      }

      // 🔹 组装列信息
      if (row.get("column_id") != null) {
        GenTableColumn column = new GenTableColumn();
        column.setColumnId(Convert.toLong(row.get("column_id")));
        column.setColumnName(Convert.toStr(row.get("column_name")));
        column.setColumnComment(Convert.toStr(row.get("column_comment")));
        column.setColumnType(Convert.toStr(row.get("column_type")));
        column.setJavaType(Convert.toStr(row.get("java_type")));
        column.setJavaField(Convert.toStr(row.get("java_field")));
        column.setIsPk(Convert.toStr(row.get("is_pk")));
        column.setIsIncrement(Convert.toStr(row.get("is_increment")));
        column.setIsRequired(Convert.toStr(row.get("is_required")));
        column.setIsInsert(Convert.toStr(row.get("is_insert")));
        column.setIsEdit(Convert.toStr(row.get("is_edit")));
        column.setIsList(Convert.toStr(row.get("is_list")));
        column.setIsQuery(Convert.toStr(row.get("is_query")));
        column.setQueryType(Convert.toStr(row.get("query_type")));
        column.setHtmlType(Convert.toStr(row.get("html_type")));
        column.setDictType(Convert.toStr(row.get("dict_type")));
        column.setSort(Convert.toInt(row.get("sort")));
        tableMap.get(tableId).getColumns().add(column);
      }
    }

    return new ArrayList<>(tableMap.values());
  }

  /**
   * 查询表ID业务信息
   *
   * @param id 业务ID
   * @return 业务信息
   */
  @Select(
      "SELECT t.table_id, t.table_name, t.table_comment, t.sub_table_name, t.sub_table_fk_name, t.class_name, t.tpl_category, t.tpl_web_type, t.package_name, t.module_name, t.business_name, t.function_name, t.function_author, t.gen_type, t.gen_path, t.options, t.remark,\n"
          + "c.column_id, c.column_name, c.column_comment, c.column_type, c.java_type, c.java_field, c.is_pk, c.is_increment, c.is_required, c.is_insert, c.is_edit, c.is_list, c.is_query, c.query_type, c.html_type, c.dict_type, c.sort\n"
          + "FROM gen_table t\n"
          + "LEFT JOIN gen_table_column c ON t.table_id = c.table_id\n"
          + "where t.table_id = #{tableId} order by c.sort")
  GenTable selectGenTableById(Long id);

  /**
   * 查询表名称业务信息
   *
   * @param tableName 表名称
   * @return 业务信息
   */
  @Select(
      "SELECT t.table_id, t.table_name, t.table_comment, t.sub_table_name, t.sub_table_fk_name, t.class_name, t.tpl_category, t.tpl_web_type, t.package_name, t.module_name, t.business_name, t.function_name, t.function_author, t.gen_type, t.gen_path, t.options, t.remark,\n"
          + "c.column_id, c.column_name, c.column_comment, c.column_type, c.java_type, c.java_field, c.is_pk, c.is_increment, c.is_required, c.is_insert, c.is_edit, c.is_list, c.is_query, c.query_type, c.html_type, c.dict_type, c.sort\n"
          + "FROM gen_table t\n"
          + "LEFT JOIN gen_table_column c ON t.table_id = c.table_id\n"
          + "where t.table_name = #{tableName} order by c.sort")
  GenTable selectGenTableByName(String tableName);

  /**
   * 新增业务
   *
   * @param genTable 业务信息
   * @return 结果
   */
  default int insertGenTable(GenTable genTable) {
    return insert(genTable);
  }

  /**
   * 修改业务
   *
   * @param genTable 业务信息
   * @return 结果
   */
  default int updateGenTable(GenTable genTable) {
    return update(genTable);
  }

  /**
   * 批量删除业务
   *
   * @param ids 需要删除的数据ID
   * @return 结果
   */
  default int deleteGenTableByIds(Long[] ids) {
    if (ids == null || ids.length == 0) {
      return 0;
    }
    return deleteBatchByIds(Arrays.asList(ids));
  }

  /**
   * 创建表
   *
   * @param sql 表结构
   * @return 结果
   */
  @Update("${sql}")
  int createTable(String sql);
}
